# -*- coding: utf-8 -*
import time
from datetime import datetime

from abc import ABC
from sqlalchemy import and_, or_
from app.configs import mysql_pool, global_config, clickhouse_pool
from app.models import db_session, largebinary_dumps
from app.models.dao import db2vectors, delete, add, db2meta, update
from app.models.model import DeviceModel, MetaModel, MetaJsonModel
from app.views import CommonHandler

persons, readers = [], []

DEVICE_TYPE_DICT = {
    'm_station': 0,
    'k_station': 1,
    'k_node':2,
    'v_camera':3
}

# 人员分站列表
def person_station_list(meta_id):
    sql = 'select * from `m_station` where `MineId`=%s'
    station_result = mysql_pool.select_all(sql, (meta_id,))
    # 将里面的时间类型转换为支持json格式
    for station in station_result:
        station['CreateTime'] = str(station['CreateTime'].isoformat()) if station['CreateTime'] is not None else None
        station['DataTime'] = str(station['DataTime'].isoformat()) if station['DataTime'] is not None else None
        station['name'] = station['Pos']
        station['children'] = []
        station['systemType'] = 0
        del station['x']
        del station['y']
        del station['z']
    return station_result


# 所有人员列表
def person_staff_list(meta_id):
    sql = 'select * from `m_staff` where `MineId`=%s'
    staff_result = mysql_pool.select_all(sql, (meta_id,))
    for staff in staff_result:
        staff['CreateTime'] = str(staff['CreateTime'].isoformat()) if staff['CreateTime'] is not None else None
        staff['DataTime'] = str(staff['DataTime'].isoformat()) if staff['DataTime'] is not None else None
        staff['EnterStationTime'] = str(staff['EnterStationTime'].isoformat()) if staff['EnterStationTime'] is not None else None
        staff['EnterAreaTime'] = str(staff['EnterAreaTime'].isoformat()) if staff['EnterAreaTime'] is not None else None
        staff['BirthDay'] = str(staff['BirthDay'].isoformat()) if staff['BirthDay'] is not None else None
        staff['InMineTime'] = str(staff['InMineTime'].isoformat()) if staff['InMineTime'] is not None else None
    return staff_result


# 监控分站列表
def sensor_station_list(meta_id):
    sql = 'select * from `k_station` where `MineID`=%s'
    station_result = mysql_pool.select_all(sql, (meta_id,))
    for station in station_result:
        station['StationData'] = float(station['StationData']) if station['StationData'] is not None else None
        station['PowerData'] = float(station['PowerData']) if station['PowerData'] is not None else None
        station['DataTime'] = str(station['DataTime'].isoformat()) if station['DataTime'] is not None else None
        station['CreateTime'] = str(station['CreateTime'].isoformat()) if station['CreateTime'] is not None else None
        station['name'] = station['StationPlace']
        station['children'] = []
        station['systemType'] = 1
        del station['X']
        del station['Y']
        del station['Z']
    return station_result


# 传感器列表
def sensor_node_list(meta_id):
    sql = 'select * from `k_node` where `MineID`=%s'
    node_result = mysql_pool.select_all(sql, (meta_id,))
    for node in node_result:
        node['BoundUpperValue'] = float(node['BoundUpperValue']) if node['BoundUpperValue'] is not None else None
        node['BoundLowerValue'] = float(node['BoundLowerValue']) if node['BoundLowerValue'] is not None else None
        node['AlarmUpperValue'] = float(node['AlarmUpperValue']) if node['AlarmUpperValue'] is not None else None
        node['AlarmLowerValue'] = float(node['AlarmLowerValue']) if node['AlarmLowerValue'] is not None else None
        node['HCutValue'] = float(node['HCutValue']) if node['HCutValue'] is not None else None
        node['LCutValue'] = float(node['LCutValue']) if node['LCutValue'] is not None else None
        node['HResetValue'] = float(node['HResetValue']) if node['HResetValue'] is not None else None
        node['LResetValue'] = float(node['LResetValue']) if node['LResetValue'] is not None else None
        node['CurrentData'] = float(node['CurrentData']) if node['CurrentData'] is not None else None
        node['AlarmLowerDisValue'] = float(node['AlarmLowerDisValue']) if node['AlarmLowerDisValue'] is not None else None
        node['AlarmUpperDisValue'] = float(node['AlarmUpperDisValue']) if node['AlarmUpperDisValue'] is not None else None
        node['ValChaRealData'] = float(node['ValChaRealData']) if node['ValChaRealData'] is not None else None
        node['YjLowerLimit'] = float(node['YjLowerLimit']) if node['YjLowerLimit'] is not None else None
        node['YjUpperLimit'] = float(node['YjUpperLimit']) if node['YjUpperLimit'] is not None else None
        node['AirVolume'] = float(node['AirVolume']) if node['AirVolume'] is not None else None
        node['DataTime'] = str(node['DataTime'].isoformat()) if node['DataTime'] is not None else None
        node['CreateTime'] = str(node['CreateTime'].isoformat()) if node['CreateTime'] is not None else None
        node['CalibrationDate'] = str(node['CalibrationDate'].isoformat()) if node['CalibrationDate'] is not None else None
        node['firstInsertTime'] = str(node['firstInsertTime'].isoformat()) if node['firstInsertTime'] is not None else None
        node['UpdateTime'] = str(node['UpdateTime'].isoformat()) if node['UpdateTime'] is not None else None
        node['ValChaStartTime'] = str(node['ValChaStartTime'].isoformat()) if node['ValChaStartTime'] is not None else None
        node['name'] = node['NodePlace']
        node['systemType'] = 2
        del node['X']
        del node['Y']
        del node['Z']
    return node_result


# 工业视频列表
def camera_list(meta_id):
    sql = 'select * from `v_camera_plus` where `mineId`=%s'
    camera_result = mysql_pool.select_all(sql, (meta_id,))
    for camera in camera_result:
        camera['updateTime'] =  str(camera['updateTime'].isoformat()) if camera['updateTime'] is not None else None
        camera['systemType'] = 3
    return camera_result


# 添加设备位置
def add_device_pos(devices,meta_id):
    session = db_session()
    for dev in devices:
        instance = DeviceModel((dev['Coordinate'][0],dev['Coordinate'][1]),meta_id,dev['device_id'])
        setattr(instance,'device_id',dev['device_id'])
        setattr(instance,'sub1',dev['sub1'])
        setattr(instance,'name',dev['name'])
        session.add(instance)
    session.commit()
    session.close()


# 修改设备位置
def update_device_pos(devices,meta_id):
    session = db_session()
    for d in devices:
        rows = session.query(DeviceModel).filter(DeviceModel.device_id == d['device_id'] and DeviceModel.meta_id == meta_id and DeviceModel.sub1 == d['sub1'])
        for r in rows:
            setattr(r,'point',largebinary_dumps(d['Coordinate']))
    session.commit()
    session.close()


def delete_devices(metaid, devices):
    session = db_session()
    for d in devices:
        session.query(DeviceModel).filter(DeviceModel.device_id == d['device_id'] and DeviceModel.meta_id == metaid and DeviceModel.sub1 == d['sub1']).delete()
    session.commit()
    session.close()


def version2Top(meta_id,version):
    session = db_session()
    versions = session.query(MetaModel).filter(and_(MetaModel.meta_id == meta_id,MetaModel.mine_name == version)).all()
    now_date = datetime.now()
    for v in versions:
        v.update_time = now_date
    meta_jsons = session.query(MetaJsonModel).filter(and_(MetaJsonModel.meta_id == meta_id,MetaJsonModel.version_id == version)).all()
    for json in meta_jsons:
        json.update_time = now_date
    session.commit()
    session.close()


def staff_track_data(meta_id, staff_id):
    translate_meta_id = '\'' + meta_id + '\''
    translate_staff_id = '\'' + staff_id + '\''
    current_date = time.strftime('%Y-%m-%d 00:00:00', time.localtime())
    # 临时把时间固定下来
    # tmp_datetime = datetime(2024,3,15,0,0,0)
    # datetime_timestap = tmp_datetime.timestamp()
    # current_date = time.strftime('%Y-%m-%d 00:00:00', time.gmtime(datetime_timestap))
    # 分割
    translate_current_date = '\'' + current_date + '\''
    sql = "select StaffStationId from m_staffrealdatatraces ms where ms.MineId ={meta_id} and ms.InMineTime > {in_mine_time} and ms.StaffId = {staff_id} order by ms.EnterStationTime".format(meta_id=translate_meta_id,in_mine_time=translate_current_date, staff_id=translate_staff_id)
    fetchs = clickhouse_pool.excute(sql)
    # 只需要把排序后的人员分站id提取出来就行了
    track_station_ids = []
    for track in fetchs:
        track_station_ids.append(track[0])
    return track_station_ids


# systemType的值对应关系为： m_station = 0， k_station = 1， k_node = 2， v_camera = 3
class TreeDataHandler(CommonHandler, ABC):
    async def get(self):
        meta_id = self.get_argument("meta_id", None)
        type = self.get_argument("type", None)
        if type == 'person':
            station_list = person_station_list(meta_id)
            staff_list = person_staff_list(meta_id)
            res = {
                'station_list': station_list,
                'staff_list': staff_list
            }
        if type == 'sensor':
            station_list = sensor_station_list(meta_id)
            node_list = sensor_node_list(meta_id)
            res = {
                'station_list': station_list,
                'node_list': node_list
            }
        if type == 'camera':
            cameras = camera_list(meta_id)
            res = {
                'camera_list': cameras
            }
        # 将人员分站和人员 组合成树结构。在前端的目录上用不上，但可以用作 人员分站的数据信息统计
        await self.response(data={'data':res})


class ShanxiDeviceHandler(CommonHandler, ABC):
    async def put(self):
        params = self.json_params()
        update_device_pos(params['devices'],params['metaId'])
        devices = db2vectors(DeviceModel, params['metaId'])
        await self.response(data=devices)

    async def post(self):
        params = self.json_params()
        add_device_pos(params['devices'],params['metaId'])
        devices = db2vectors(DeviceModel, params['metaId'])
        # 将最新的device信息返回前端，让前端对device表格进行本地更新
        await self.response(data=devices)

    async def get(self):
        meta_id = self.get_argument('metaId')
        devices_p_infos = db2vectors(DeviceModel,meta_id)
        await self.response(data={'device_pos_infos': devices_p_infos})

    async def delete(self):
        params = self.json_params()
        devices = params['devices']
        meta_id = params['metaId']
        for device in devices:
            device['sub1'] = DEVICE_TYPE_DICT[device['device_type']]
        delete_devices(meta_id, devices)
        devices_p_infos = db2vectors(DeviceModel,meta_id)
        await self.response(data=devices_p_infos)


class StaffDataHandler(CommonHandler, ABC):
    async def get(self):
        meta_id = self.get_argument("meta_id", None)
        staffs = person_staff_list(meta_id)
        await self.response(data=staffs)


class KstationDataHandler(CommonHandler, ABC):
    async def get(self):
        meta_id = self.get_argument("meta_id", None)
        kstations = sensor_station_list(meta_id)
        await self.response(data=kstations)


class KnodeDataHandler(CommonHandler, ABC):
    async def get(self):
        meta_id = self.get_argument("meta_id", None)
        sensor_nodes = sensor_node_list(meta_id)
        await self.response(data=sensor_nodes)


class VcameraDataHandler(CommonHandler, ABC):
    async def get(self):
        meta_id = self.get_argument("meta_id", None)
        cameras = camera_list(meta_id)
        await self.response(data=cameras)


class StaffTrackHandler(CommonHandler, ABC):
    async def get(self):
        meta_id = self.get_argument("meta_id", None)
        staff_id = self.get_argument("staff_id", None)
        tracks = staff_track_data(meta_id,staff_id)
        await self.response(data=tracks)


class MetaVersion2TopHandler(CommonHandler, ABC):
    async def put(self):
        params = self.json_params()
        meta_id = params['meta_id']
        meta_version = params['meta_version']
        version2Top(meta_id,meta_version)
        await self.response()